
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

/******************************************************************************
 * File:NewJFrame.java
 * Course: 17655
 * Project: Assignment 2
 * Copyright: Copyright (c) 2009 Carnegie Mellon University
 * Versions:
 *	1.0 November 2009 - Initial rewrite of original assignment 2 (ajl).
 *
 * This class defines a GUI application that allows inventory to be added to the
 * inventory databases. There are tree tables in the inventory database: trees,
 * shrubs, and seeds.
 *
 ******************************************************************************/
/*
 * AddInventoryMainFrame.java
 *
 * Created on Jan 29, 2010, 9:24:23 PM
 */

/**
 *
 * @author lattanze
 */
public class AddInventoryMainFrame extends javax.swing.JFrame {

       String versionID = "v2.10.10";

    /** Creates new form AddInventoryMainFrame */
    public AddInventoryMainFrame() {
        initComponents();
        jLabel1.setText("Add Inventory Application " + versionID);
    }

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jRadioButton1 = new javax.swing.JRadioButton();
        jRadioButton2 = new javax.swing.JRadioButton();
        jRadioButton3 = new javax.swing.JRadioButton();
        jLabel1 = new javax.swing.JLabel();
        jLabel2 = new javax.swing.JLabel();
        jLabel3 = new javax.swing.JLabel();
        jLabel4 = new javax.swing.JLabel();
        jTextField1 = new javax.swing.JTextField();
        jTextField2 = new javax.swing.JTextField();
        jTextField3 = new javax.swing.JTextField();
        jTextField4 = new javax.swing.JTextField();
        jButton1 = new javax.swing.JButton();
        jSeparator1 = new javax.swing.JSeparator();
        jScrollPane1 = new javax.swing.JScrollPane();
        jTextArea1 = new javax.swing.JTextArea();
        jLabel5 = new javax.swing.JLabel();
        jLabel6 = new javax.swing.JLabel();
        jTextField5 = new javax.swing.JTextField();
        jButton2 = new javax.swing.JButton();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

        jRadioButton1.setText("Trees");
        jRadioButton1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jRadioButton1ActionPerformed(evt);
            }
        });

        jRadioButton2.setText("Shrubs");
        jRadioButton2.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jRadioButton2ActionPerformed(evt);
            }
        });

        jRadioButton3.setText("Seeds");
        jRadioButton3.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jRadioButton3ActionPerformed(evt);
            }
        });

        jLabel1.setText("Add Inventory Application");

        jLabel2.setText("Product ID");

        jLabel3.setText("Price");

        jLabel4.setText("Quantity");

        jTextField1.setText("localhost");

        jButton1.setText("Submit");
        jButton1.setActionCommand("jButton1");
        jButton1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton1ActionPerformed(evt);
            }
        });

        jTextArea1.setColumns(20);
        jTextArea1.setRows(5);
        jScrollPane1.setViewportView(jTextArea1);

        jLabel5.setText("Product Description");

        jLabel6.setText("Database Server IP");

        jTextField5.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jTextField5ActionPerformed(evt);
            }
        });

        jButton2.setText("List Inventory");
        jButton2.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton2ActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addContainerGap()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addGroup(layout.createSequentialGroup()
                                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                                    .addComponent(jLabel4)
                                    .addComponent(jLabel2)
                                    .addComponent(jLabel5)
                                    .addComponent(jLabel3))
                                .addGap(18, 18, 18)
                                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                    .addGroup(layout.createSequentialGroup()
                                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                                            .addComponent(jTextField3)
                                            .addComponent(jTextField4)
                                            .addComponent(jTextField2, javax.swing.GroupLayout.PREFERRED_SIZE, 137, javax.swing.GroupLayout.PREFERRED_SIZE))
                                        .addGap(26, 26, 26)
                                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                            .addComponent(jRadioButton1)
                                            .addGroup(layout.createSequentialGroup()
                                                .addComponent(jRadioButton3)
                                                .addGap(27, 27, 27))
                                            .addGroup(layout.createSequentialGroup()
                                                .addComponent(jRadioButton2)
                                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)))
                                        .addGap(30, 30, 30)
                                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                                            .addComponent(jButton2, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                            .addComponent(jButton1, javax.swing.GroupLayout.DEFAULT_SIZE, 165, Short.MAX_VALUE)))
                                    .addComponent(jTextField5, javax.swing.GroupLayout.DEFAULT_SIZE, 440, Short.MAX_VALUE)))
                            .addComponent(jSeparator1, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.DEFAULT_SIZE, 551, Short.MAX_VALUE)
                            .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 551, Short.MAX_VALUE)))
                    .addGroup(layout.createSequentialGroup()
                        .addGap(223, 223, 223)
                        .addComponent(jLabel1))
                    .addGroup(layout.createSequentialGroup()
                        .addContainerGap()
                        .addComponent(jLabel6)
                        .addGap(17, 17, 17)
                        .addComponent(jTextField1, javax.swing.GroupLayout.PREFERRED_SIZE, 134, javax.swing.GroupLayout.PREFERRED_SIZE)))
                .addContainerGap())
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jLabel1)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 47, Short.MAX_VALUE)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                    .addGroup(layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(jLabel6)
                            .addComponent(jTextField1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addGap(18, 18, 18)
                        .addComponent(jLabel5))
                    .addComponent(jTextField5, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(jTextField2, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(jRadioButton1)
                            .addComponent(jLabel2))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(jTextField3, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(jRadioButton3)
                            .addComponent(jLabel3))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(jTextField4, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(jRadioButton2)
                            .addComponent(jLabel4)))
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(jButton1, javax.swing.GroupLayout.PREFERRED_SIZE, 31, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                        .addComponent(jButton2, javax.swing.GroupLayout.PREFERRED_SIZE, 31, javax.swing.GroupLayout.PREFERRED_SIZE)))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jSeparator1, javax.swing.GroupLayout.PREFERRED_SIZE, 10, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(18, 18, 18)
                .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 240, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap())
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void jRadioButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jRadioButton1ActionPerformed
        // TODO add your handling code here:

        jRadioButton2.setSelected(false);
        jRadioButton3.setSelected(false);

    }//GEN-LAST:event_jRadioButton1ActionPerformed

    private void jRadioButton3ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jRadioButton3ActionPerformed
        // TODO add your handling code here:
        jRadioButton1.setSelected(false);
        jRadioButton2.setSelected(false);
    }//GEN-LAST:event_jRadioButton3ActionPerformed

    private void jRadioButton2ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jRadioButton2ActionPerformed
        // TODO add your handling code here:
        jRadioButton1.setSelected(false);
        jRadioButton3.setSelected(false);
    }//GEN-LAST:event_jRadioButton2ActionPerformed

    private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed
        // TODO add your handling code here:

        Boolean connectError = false;   // Error flag
        Connection DBConn = null;       // MySQL connection handle
        String description;             // Tree, seed, or shrub description
        Boolean executeError = false;   // Error flag
        String errString = null;        // String for displaying errors
        int executeUpdateVal;           // Return value from execute indicating effected rows
        Boolean fieldError = true;      // Error flag
        String msgString = null;        // String for displaying non-error messages
        ResultSet res = null;           // SQL query result set pointer
        String tableSelected = null;    // String used to determine which data table to use
        Integer quantity;               // Quantity of trees, seeds, or shrubs
        Float perUnitCost;              // Cost per tree, seed, or shrub unit
        String productID = null;        // Product id of tree, seed, or shrub
        java.sql.Statement s = null;    // SQL statement pointer
        String SQLstatement = null;     // String for building SQL queries
        
        // Check to make sure a radio button is selected
        if (jRadioButton1.isSelected() | jRadioButton2.isSelected() | jRadioButton3.isSelected())
        {
            fieldError = false;
        } else {

            msgString = "Must select Tree, Seeds, or Shrubs radio button.";
            jTextArea1.setText("\n"+msgString);
        }

        //Make sure there is a product description
        if ( jTextField5.getText().length() > 0 )
        {
            fieldError = false;
        } else {
            
            msgString = "Must enter a product description.";
            jTextArea1.setText("\n"+msgString);
        }
        
        //Make sure there is a product ID
        if ( jTextField2.getText().length() > 0 )
        {
            fieldError = false;
        } else {
            
            msgString = "Must enter a product ID.";
            jTextArea1.setText("\n"+msgString);
        }

        //Make sure there is a price
        if ( jTextField3.getText().length() > 0 )
        {
            fieldError = false;
        } else {

            msgString = "Must enter a product price.";
            jTextArea1.setText("\n"+msgString);
        }

        //Make sure there is a quantity
        if ( jTextField4.getText().length() > 0 )
        {
            fieldError = false;
        } else {

            msgString = "Must enter a product quantity.";
            jTextArea1.setText("\n"+msgString);
        }

        //Now, if there was no error in the data fields, we try to
        //connect to the database.
        if ( !fieldError )
        {
            try
            {
                msgString = ">> Establishing Driver...";
                jTextArea1.setText("\n"+msgString);

                //load JDBC driver class for MySQL
                Class.forName( "com.mysql.jdbc.Driver" );

                msgString = ">> Setting up URL...";
                jTextArea1.append("\n"+msgString);

                //define the data source
                String SQLServerIP = jTextField1.getText();
                String sourceURL = "jdbc:mysql://" + SQLServerIP + ":3306/inventory";

                msgString = ">> Establishing connection with: " + sourceURL + "...";
                jTextArea1.append("\n"+msgString);

                //create a connection to the db
                DBConn = DriverManager.getConnection(sourceURL,"remote","remote_pass");

            } catch (Exception e) {

                errString =  "\nProblem connecting to database:: " + e;
                jTextArea1.append(errString);
                connectError = true;

            } // end try-catch
        } // fieldError check

        //If there is not connection error, then we form the SQL statement
        //and then execute it.

        if (!connectError && !fieldError )
        {
            try
            {
                // get the data from the text fields
                description = jTextField5.getText();
                productID = jTextField2.getText();
                quantity = Integer.parseInt(jTextField4.getText());
                perUnitCost = Float.parseFloat(jTextField3.getText());

                // create an SQL statement variable and create the INSERT
                // query to insert the new inventory into the database

                s = DBConn.createStatement();

                // if trees are selected then insert inventory into trees
                // table
                if (jRadioButton1.isSelected())
                {
                    SQLstatement = ( "INSERT INTO trees (product_code, " +
                            "description, quantity, price) VALUES ( '" +
                            productID + "', " + "'" + description + "', " +
                            quantity + ", " + perUnitCost + ");");

                }

                // if shrubs are selected then insert inventory into strubs
                // table
                if (jRadioButton2.isSelected())
                {
                    SQLstatement = ( "INSERT INTO shrubs (product_code, " +
                            "description, quantity, price) VALUES ( '" +
                            productID + "', " + "'" + description + "', " +
                            quantity + ", " + perUnitCost + ");");
                }

                // if seeds are selected then insert inventory into seeds
                // table
                if (jRadioButton3.isSelected())
                {
                    SQLstatement = ( "INSERT INTO seeds (product_code, " +
                            "description, quantity, price) VALUES ( '" +
                            productID + "', " + "'" + description + "', " +
                            quantity + ", " + perUnitCost + ");");
                }

                // execute the update
                executeUpdateVal = s.executeUpdate(SQLstatement);

                // let the user know all went well
                errString =  "\nINVENTORY UPDATED...";
                jTextArea1.append(errString);

            } catch (Exception e) {

                errString =  "\nProblem with insert:: " + e;
                jTextArea1.append(errString);
                executeError = true;

            } // try

        } //execute SQL check

        //If the execute when OK, then we lList the contents of the table

        if ( !connectError && !fieldError && !executeError )
        {
            try
            {
                // create another SQL statement
                s = DBConn.createStatement();

                // now we build a query to list the inventory table contents
                // for the user
                // ... here is the SQL for trees
                if (jRadioButton1.isSelected())
                {
                    res = s.executeQuery( "Select * from trees" );
                    tableSelected = "TREE";
                }
                // ... here is the SQL for shrubs
                if (jRadioButton2.isSelected())
                {
                    res = s.executeQuery( "Select * from shrubs" );
                    tableSelected = "SHRUB";
                }
                // ... here is the SQL for seeds
                if (jRadioButton3.isSelected())
                {
                    res = s.executeQuery( "Select * from seeds" );
                    tableSelected = "SEED";
                }

                // Now we list the inventory for the selected table
                jTextArea1.setText("");
                while (res.next())
                {
                    msgString = tableSelected + ">> " + res.getString(1) + " :: " + res.getString(2) +
                            " :: "+ res.getString(3) + " :: " + res.getString(4);
                    jTextArea1.append("\n"+msgString);

                } // while

            } catch(Exception e) {

                errString =  "\nProblem with " + tableSelected +" query:: " + e;
                jTextArea1.append(errString);
                executeError = true;

            } // try
        }
    }//GEN-LAST:event_jButton1ActionPerformed

    private void jTextField5ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jTextField5ActionPerformed
        // TODO add your handling code here:
    }//GEN-LAST:event_jTextField5ActionPerformed

    private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton2ActionPerformed
        // This button will list the inventory for the product selected by the
        // radio button

        Boolean connectError = false;   // Error flag
        Connection DBConn = null;       // MySQL connection handle
        Boolean executeError = false;   // Error flag
        String errString = null;        // String for displaying errors
        Boolean fieldError = true;      // Error flag
        String msgString = null;        // String for displaying non-error messages
        ResultSet res = null;           // SQL query result set pointer
        String tableSelected = null;    // String used to determine which data table to use
        java.sql.Statement s = null;    // SQL statement pointer

        // Check to make sure a radio button is selected
        if (jRadioButton1.isSelected() | jRadioButton2.isSelected() | jRadioButton3.isSelected())
        {
            fieldError = false;

        } else {

            msgString = "Must select Tree, Seeds, or Shrubs radio button.";
            jTextArea1.setText("\n"+msgString);
        }


        //Now, we try to connect to the inventory database.
        if (!fieldError)
        {
            //Clear the fields - they are not needed and may cause confusion
            jTextField2.setText("");
            jTextField3.setText("");
            jTextField4.setText("");
            jTextField5.setText("");
            jTextArea1.setText("");
            
            try
            {
                msgString = ">> Establishing Driver...";
                jTextArea1.setText("\n"+msgString);

                //load JDBC driver class for MySQL
                Class.forName( "com.mysql.jdbc.Driver" );

                msgString = ">> Setting up URL...";
                jTextArea1.append("\n"+msgString);

                //define the data source
                String SQLServerIP = jTextField1.getText();
                String sourceURL = "jdbc:mysql://" + SQLServerIP + ":3306/inventory";

                msgString = ">> Establishing connection with: " + sourceURL + "...";
                jTextArea1.append("\n"+msgString);

                //create a connection to the db
                DBConn = DriverManager.getConnection(sourceURL,"remote","remote_pass");

            } catch (Exception e) {

                errString =  "\nProblem connecting to database:: " + e;
                jTextArea1.append(errString);
                connectError = true;

            } // end try-catch

        } // fielderror check - make sure a product is selected
    
        //If there is not connection error, then we form the SQL statement
        //and then execute it.

        if (!connectError && !fieldError)
        {
            try
            {
                // create an SQL statement variable and create the INSERT
                // query to insert the new inventory into the database

                s = DBConn.createStatement();

                // now we build a query to list the inventory table contents
                // for the user
                // ... here is the SQL for trees
                if (jRadioButton1.isSelected())
                {
                    res = s.executeQuery( "Select * from trees" );
                    tableSelected = "TREE";
                }
                // ... here is the SQL for shrubs
                if (jRadioButton2.isSelected())
                {
                    res = s.executeQuery( "Select * from shrubs" );
                    tableSelected = "SHRUB";
                }
                // ... here is the SQL for seeds
                if (jRadioButton3.isSelected())
                {
                    res = s.executeQuery( "Select * from seeds" );
                    tableSelected = "SEED";
                }

                // Now we list the inventory for the selected table
                jTextArea1.setText("");
                while (res.next())
                {
                    msgString = tableSelected+">> " + res.getString(1) + " :: " + res.getString(2) +
                            " :: "+ res.getString(3) + " :: " + res.getString(4);
                    jTextArea1.append("\n"+msgString);

                } // while

            } catch(Exception e) {

                errString =  "\nProblem with " + tableSelected +" query:: " + e;
                jTextArea1.append(errString);
                executeError = true;

            } // try
        }
    }//GEN-LAST:event_jButton2ActionPerformed

    /**
    * @param args the command line arguments
    */
    public static void main(String args[]) {
        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                new AddInventoryMainFrame().setVisible(true);
            }
        });
    }

    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton jButton1;
    private javax.swing.JButton jButton2;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabel3;
    private javax.swing.JLabel jLabel4;
    private javax.swing.JLabel jLabel5;
    private javax.swing.JLabel jLabel6;
    private javax.swing.JRadioButton jRadioButton1;
    private javax.swing.JRadioButton jRadioButton2;
    private javax.swing.JRadioButton jRadioButton3;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JSeparator jSeparator1;
    private javax.swing.JTextArea jTextArea1;
    private javax.swing.JTextField jTextField1;
    private javax.swing.JTextField jTextField2;
    private javax.swing.JTextField jTextField3;
    private javax.swing.JTextField jTextField4;
    private javax.swing.JTextField jTextField5;
    // End of variables declaration//GEN-END:variables

}
